from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, SMALLINT, Integer, String, Float, FLOAT, Text, TIMESTAMP, DATETIME,TIME
from Common.dbhelper import DBHelper
from flask_jsontools import JsonSerializableBase

Base = declarative_base(cls=(JsonSerializableBase,))


class PvMeta(Base):
    __tablename__ = 'pvpannel_meta'
    id = Column(Integer, primary_key=True,autoincrement=True)
    device_num = Column(String(80), nullable=False)
    current_val = Column(FLOAT(10), nullable=False,default=0.0)
    voltage_val = Column(FLOAT(10), nullable=False,default=0.0)
    login_time = Column(Integer)

    __table_args__ = {"mysql_charset": "utf8"}

    def __repr__(self):
        return "<PvMeta>{}:{},{}".format(self.device_num, self.current_val, self.voltage_val)

    def toJson(self):
        dict = self.__dict__
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Device_info(Base):  # 设备信息
    __tablename__ = 'device_info_10001'
    id = Column(Integer, primary_key=True, autoincrement=True)
    device_id = Column(Text, nullable=False, default="")
    device_name = Column(Text, nullable=False, default="")
    devtype = Column(SMALLINT, nullable=False, default=0)
    capacity = Column(Float, nullable=False, default=0.0)
    rated_out_p = Column(Float, nullable=False, default=0.0)
    rated_in_p = Column(Float, nullable=False, default=0.0)
    max_outpower = Column(Float, nullable=False, default=0.0)
    max_inpower = Column(Float, nullable=False, default=0.0)
    time_last = Column(SMALLINT, nullable=False, default=3600)
    max_outqpower = Column(Float, nullable=False, default=0.0)
    max_life = Column(SMALLINT, nullable=False, default=20)
    in_mode = Column(Text, nullable=False, default="AC")
    out_mode = Column(Text, nullable=False, default="AC")
    time_in_service = Column(Text, nullable=False, default="")
    controllable_vars = Column(Text, nullable=False, default="")


    __table_args__ = {"mysql_charset": "utf8"}

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    # def __init__(self, device_no, devtype, devname, **kwargs):
    #     self.device_no = device_no
    #     self.devtype   = devtype
    #     self.devname   = devname
    #     self.online    = 0
    #     self.master    = None
    #     self.master_name = None
    #     self.slaveID     = 1


class Electric_price(Base):  # 电价信息
    __tablename__ = 'electric_price_10003'
    id = Column(Integer, primary_key=True, autoincrement=True)
    time_stamp = Column(Float, nullable=False, default=0.0)
    price = Column(Float, nullable=False, default=0.0)  # 单位元

    __table_args__ = {"mysql_charset": "utf8"}

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    # segment_id = Column(SMALLINT, nullable=False, default=0)
    # segment_type = Column(Text, nullable=False, default="flat")
    # t_start = Column(Float, nullable=False, default=0.0)
    # t_stop = Column(Float, nullable=False, default=0.0)
    # price = Column(Float, nullable=False, default=0.0)
    # date_start = Column(Text, nullable=False, default="2019-01-01 00:00:00")
    # date_end = Column(Text, nullable=False, default="2019-01-01 00:00:00")
    # capacity_price = Column(Float, nullable=False, default=0.0)


class Load_data(Base):  # 负荷信息
    __tablename__ = 'load_data_10103'
    id = Column(Integer, primary_key=True, autoincrement=True)
    time_stamp = Column(Float, nullable=False, default="")
    load_p = Column(Float, nullable=False, default=0.0)
    load_q = Column(Float, nullable=False, default=0.0)

    __table_args__ = {"mysql_charset": "utf8"}

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class PV_forecast(Base):  # 光伏出力预测
    __tablename__ = 'pv_forecast_10104'
    id = Column(Integer, primary_key=True, autoincrement=True)
    time_stamp = Column(Float, nullable=False, default="")
    out_p_sunny = Column(Float, nullable=False, default=0.0)  # 晴天
    out_p_cloudy = Column(Float, nullable=False, default=0.0)  # 多云
    out_p_overcast = Column(Float, nullable=False, default=0.0)  # 阴雨

    __table_args__ = {"mysql_charset": "utf8"}

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Day_Ahead(Base):  # 日计划
    __tablename__ = 'day_ahead_10402'
    id = Column(Integer, primary_key=True, autoincrement=True)
    device_id = Column(Text, nullable=False, default="")
    type = Column(Text, nullable=False, default="DayAhead")
    time_stamp = Column(DATETIME, nullable=False, default=None)
    p_value = Column(Text, nullable=False, default="")
    q_value = Column(Text, nullable=False, default="")

    __table_args__ = {"mysql_charset": "utf8"}

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Realtime_commands(Base):  # 日计划
    __tablename__ = 'realtime_commands_10401'
    id = Column(Integer, primary_key=True, autoincrement=True)
    device_id = Column(Text, nullable=False, default="")
    type = Column(Text, nullable=False, default="Realtime")
    time_stamp = Column(DATETIME, nullable=False, default=None)
    p_value = Column(Float, nullable=False, default=0.0)
    q_value = Column(Float, nullable=False, default=0.0)

    __table_args__ = {"mysql_charset": "utf8"}

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}



def init_db():
    dbhelper = DBHelper()
    # Base.metadata.engine = dbhelper.engine
    Base.metadata.create_all(dbhelper.engine)
    # new_data1 = Device_info(device_id="1", device_name="Serial3.光伏逆变器pv",
    #                        devtype=221, capacity=0.4, rated_out_p=2.0,
    #                        rated_in_p=2.0, max_outpower=2.0, max_inpower=2.0,
    #                        time_last=100, max_outqpower=0.0, max_life=20, time_in_service="2018-10-31 00:00:00",
    #                        controllable_vars='["充电开始时间","充电结束时间","放电开始时间","放电结束时间",'
    #                                          '"充放电功率限制","输出功率因数","有功输出","无功输出"]'
    #                        )
    # new_data2 = Device_info(device_id="2", device_name="Serial4.储能battery",
    #                        devtype=111, capacity=5.0, rated_out_p=2.5,
    #                        rated_in_p=2.0, max_outpower=2.5, max_inpower=2.0,
    #                        time_last=100, max_outqpower=0.5, max_life=20, time_in_service="2018-10-31 00:00:00",
    #                        controllable_vars='["充电开始时间","充电结束时间","放电开始时间","放电结束时间",'
    #                                          '"充放电功率限制","输出功率因数"]'
    #                        )
    # new_data3 = Device_info(device_id="3", device_name="Serial5.电能表",
    #                        devtype=401, capacity=0.0, rated_out_p=0,
    #                        rated_in_p=0, max_outpower=0, max_inpower=0,
    #                        time_last=100, max_outqpower=0, max_life=20, time_in_service="2018-10-31 00:00:00",
    #                        controllable_vars=''
    #                        )
#    new_data = []
#     for i in range(23):
#         if i<7:
#             new_data = Electric_price(time_stemp=i, price=0.2612)
#         elif 6<i<11 or 18<i<23:
#             new_data = Electric_price(time_stemp=i, price=0.7836)
#         else:
#             new_data = Electric_price(time_stemp=i, price=0.5224)
#         dbhelper.Session.add(new_data)
# #     # dbhelper.Session.add(new_data2)
# #     # dbhelper.Session.add(new_data3)
#     for i in range(23):
#         if 6<i<11:
#             new_data = Load_data(time_stemp=i, load_p=i/10.0, load_q=0.0)
#         elif 10<i<16:
#             new_data = Load_data(time_stemp=i, load_p=1.0+(i-10)*0.05, load_q=0.0)
#         elif 15<i<20:
#             new_data = Load_data(time_stemp=i, load_p=1.25-(i-15)*0.25, load_q=0.0)
#         else:
#             new_data = Load_data(time_stemp=i, load_p=0.0, load_q=0.0)
#         dbhelper.Session.add(new_data)
#
    # pv_data = [0.0, 0.0, 0.0, 0.0, 0.0, 0.0,
    #              8.4515125000000015, 29.798410833333332, 73.734966666666708, 96.74186916666666,
    #              138.00852249999991, 174.39009249999989, 183.68381333333343, 144.2782666666667,
    #              108.43244750000002, 80.250684166666687, 55.094855833333334, 61.077373333333313,
    #              48.906822500000004, 21.339119999999998, 2.5665116666666679, 0.0, 0.0, 0.0]
    # for i in range(24):
    #     newdata=PV_forecast(time_stamp=i, out_p=pv_data[i]/100)
    #     dbhelper.Session.add(newdata)
    # load = [50.54,50.54,50.54,50.54,50.54,50.54,41.93,58.89,82.34,93.2,101.94,101.94,101.94,101.94,101.94,
    #         101.94,101.94,95.5,75.47,70.39,57.45,51.21,50.54,50.54]
    # for i in range(24):
    #     data = dbhelper.Session.query(Load_data).filter_by(id=i+1).first()
    #     data.load_p = load[i]/100
    #
    # cloudy = [0,0,0,0,0,0,6.4515125,25.79841083,65.73496667,76.74186917,78.0085225,74.3900925,73.68381333,78.27826667,
    #             77.1324475,70.25068417,75.09485583,60.07737333,38.9068225,18.33912,1.566511667,0,0,0]
    # overcast = [0,0,0,0,0,0,1.4515125,2.798410833,7.734966667,5.741869167,8.90085225,6.3900925,3.683813333,8.278266667,
    #             7.1324475,3.250684167,5.094855833,6.077373333,3.9068225,2.33912,0.566511667,0,0,0]
    #
    # for i in range(24):
    #     data = dbhelper.Session.query(PV_forecast).filter_by(id=i+1).first()
    #     data.out_p_cloudy = cloudy[i]/100
    #     data.out_p_overcast = overcast[i]/100
    #
    # dbhelper.Session.commit()


if __name__ == "__main__":
    init_db()



# Base = declarative_base()
#
# from coroutine.common import data_tables  # 数据表的申明要放在Base.metadata.create_all(engine)之前，不然创建表不成功
#
# engine = create_engine('mysql+pymysql://root:root@localhost/mbiot?charset=utf8', encoding='utf-8')  #
# Base.metadata.engine = engine
# Base.metadata.create_all(engine)
# DBSessionMaker = sessionmaker(bind=engine)
# session = DBSessionMaker()
